﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class User
    {
        public User()
        {
        }

        public List<UserInfo> GetList(string value, string AppCode)
        {
            List<UserInfo> list = new List<UserInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = "SELECT * FROM V_USER WHERE ((NIP LIKE '%" + value + "%') OR (USER_NAME LIKE '%" + value + "%')) AND (APP_ID LIKE '" + AppCode + "');";
            DbCommand cmd;
            cmd = db.GetSqlStringCommand(sql);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), CharToBool(dr.GetString(8)), CharToBool(dr.GetString(9)), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12)));
                }
                dr.Close();
            }
            return list;
        }

        public List<UserInfo> GetListPaging(string value, string AppCode, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<UserInfo> list = new List<UserInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT * FROM
                          (
                               SELECT ROW_NUMBER() OVER (ORDER BY NIP) AS Row, * 
                               FROM V_USER 
                               WHERE ((NIP LIKE @PARAM) OR (USER_NAME LIKE @PARAM)) 
                                    AND (APP_ID = @APP_ID)
                          ) AS LogWithRowNumbers 
                           WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR;";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new UserInfo(dr.GetInt32(1), dr.GetString(2).Trim(), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetInt32(8), CharToBool(dr.GetString(9)), CharToBool(dr.GetString(10)), dr.GetDateTime(11), dr.GetString(12), dr.GetString(13)));
                }
                dr.Close();
            }
            return list;
        }

        public int GetRowListPaging(string value, string AppCode)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT COUNT(1) AS jmlData
                            FROM V_USER (NOLOCK)
                           WHERE ((NIP LIKE @PARAM) OR (USER_NAME LIKE @PARAM)) 
                                AND (APP_ID = @APP_ID)";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public UserInfo GetByID(Int32 UserID, string AppCode)
        {
            UserInfo user = new UserInfo();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;

            sql = "SELECT * FROM V_USER WHERE (USER_ID = " + UserID + ") AND (APP_ID = '" + AppCode + "');";
            cmd = db.GetSqlStringCommand(sql);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user = new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), CharToBool(dr.GetString(8)), CharToBool(dr.GetString(9)), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return user;
        }

        //-- Dibuat tapi belum digunakan, keperluan untuk bypass APP_ID pada V_USER
        //-- bypass agar tidak dibatasi hanya pada SPADA dan SPAWEB
        public UserInfo GetAllUser(string nip)
        {
            UserInfo user = new UserInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd;

            string sql = @"EXEC GET_ALL_USER_VIEW_ALL @NIP";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user = new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), CharToBool(dr.GetString(8)), CharToBool(dr.GetString(9)), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return user;
        }
        //-------------------------------------------------------------------------

        public UserInfo Get(string nip, string AppCode)
        {
            UserInfo user = new UserInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd;

            string sql = @"EXEC GET_ALL_USER @NIP, @APP_ID";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user = new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), CharToBool(dr.GetString(8)), CharToBool(dr.GetString(9)), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return user;
        }

        public int GetHubSpokeUser(string nip)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT COUNT(1) AS jmlData
                            FROM SPAAdmin.DBO.TBL_MS_UNDERWRITER A
                            INNER JOIN SPAAdmin.DBO.TBL_USER B ON A.NIP = B.NIP
                            INNER JOIN SPAAdmin.DBO.TBL_USER_APPLICATION C ON C.USER_ID = B.USER_ID
                            WHERE (C.APP_ID = 'WINCPA' OR C.APP_ID = 'WEBCPA') AND A.NIP = @NIP";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public UserInfo GetLoginInfo(string nip, string AppCode)
        {
            UserInfo user = new UserInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd;

            string sql = @"EXEC GET_LOGIN_USER_INFO @NIP, @APP_ID";
            cmd = db.GetSqlStringCommand(sql);
            cmd.CommandTimeout = 600;

            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user = new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), CharToBool(dr.GetString(8)), CharToBool(dr.GetString(9)), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return user;
        }

        public string GetUserAlternate(string nip, string AppCode)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd;

            string sql = @"EXEC GET_USER_ALTERNATE @NIP, @APP_ID";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            object result = db.ExecuteScalar(cmd);
            if (result == null) return string.Empty;
            else return result.ToString();
        }

        public bool CekExistingAlternate(string UNIT_CODE, string USER_TYPE, string NIP)
        {
            object result;
            Database db = DBConnection.CreateDatabase();
            string sql = @"	DECLARE @PROCESS_DATE DATETIME 
                                    SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 
        
        	                        SELECT  COUNT(*)
                                    FROM    TBL_ALTERNATE_UM
                                    WHERE	NIP <> @NIP
                                            AND LTRIM(RTRIM(UPPER(BRANCH_ID)))=@BRANCH_ID 
                                            AND LTRIM(RTRIM(UPPER(ALTERNATE_FOR)))=@USER_TYPE 
                                            AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
        			                        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                                            AND IS_ACTIVE = '1'";
            DbCommand cmd;
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, UNIT_CODE);
            db.AddInParameter(cmd, "@USER_TYPE", DbType.String, USER_TYPE);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            result = db.ExecuteScalar(cmd);
            if (result == null) return false;
            else if (Convert.ToInt32(result) > 0) return true;
            return false;
        }

        public List<UserInfo> GetUserCO(string UNIT_CODE)
        {
            List<UserInfo> user = new List<UserInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT USER_ID, AO_CODE, NIP, USER_NAME
                           FROM V_USER WHERE (LTRIM(RTRIM(BRANCH_ID)) LIKE @BRANCH_ID) 
                                  AND (LTRIM(RTRIM(USER_TYPE)) ='CO') 
                                 GROUP BY USER_ID, AO_CODE, NIP, USER_NAME";
            DbCommand cmd;
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, UNIT_CODE);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user.Add(new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3)));
                }
                dr.Close();
            }
            return user;
        }

        public List<UserInfo> GetUserSO(string UNIT_CODE)
        {
            List<UserInfo> user = new List<UserInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT USER_ID, AO_CODE, NIP, AO_CODE + ' - ' + USER_NAME AS USER_NAME
                           FROM V_USER WHERE (LTRIM(RTRIM(BRANCH_ID)) LIKE @BRANCH_ID) 
                                  AND (LTRIM(RTRIM(USER_TYPE)) IN ('SO','RO')) 
                                 GROUP BY USER_ID, AO_CODE, NIP, USER_NAME";
            DbCommand cmd;
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, UNIT_CODE);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user.Add(new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3)));
                }
                dr.Close();
            }
            return user;
        }

        public void UserUpdate(UserInfo userInfo)
        {
            Database db = DBConnection.CreateDatabase();
            UserInfo user = new UserInfo();

            string sql;
            DbCommand cmd;

            sql = @"EXEC USER_UPDATE @USER_ID, @APP_ID, @IS_LOGIN, @LOGIN_ADDRESS";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@USER_ID", DbType.Int32, userInfo.Id);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, userInfo.AppId);
            db.AddInParameter(cmd, "@IS_LOGIN", DbType.String, BoolToChar(userInfo.IsLogin));
            db.AddInParameter(cmd, "@LOGIN_ADDRESS", DbType.String, userInfo.LoginAddr);

            db.ExecuteNonQuery(cmd);
        }

        public string GetUserPrivilege(int userId, int menuId, string AppCode)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd;

            string sql = "EXEC GET_PRIVILEGE_BY_USER @APP_ID, @USER_ID, @MENU_ID";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            db.AddInParameter(cmd, "@USER_ID", DbType.Int32, userId);
            db.AddInParameter(cmd, "@MENU_ID", DbType.Int32, menuId);

            string result = (string)db.ExecuteScalar(cmd);

            return result;
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        private string BoolToChar(bool source)
        {
            if (source)
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }

        public List<UserInfo> GetUserGroupName(string NIP)
        {
            List<UserInfo> list = new List<UserInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;

                sql = @"SELECT [NIP],[USER_NAME]
                            ,[APP_ID],[GROUP_ID],[GROUP_NAME]
                        FROM [V_USER_APP] WHERE NIP=@NIP 
                        GROUP BY [NIP],[USER_NAME],[APP_ID],[GROUP_ID],[GROUP_NAME]";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new UserInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt32(3), dr.GetString(4)));
                }
                dr.Close();
            }
            return list;
        }
    }
}